(ns clj-gallery.models.db
  (:refer-clojure :exclude [update])
  (:require [clojure.java.jdbc :as sql]
            [korma.db :refer [defdb transaction]]
            [korma.core :refer [defentity insert select update delete values limit where exec-raw]]))

(declare users images)

(def db {:subprotocol "sqlite"
         :subname     "./gallery.sqlite"})

(defdb korma-db db)

(defentity users)
(defentity images)

#_(defn create-user [user]
    (sql/insert! db :users user))
(defn create-user [user]
  (insert users (values user)))

#_(defn get-user [id]
    (-> (sql/query db ["select * from users where id = ?" id])
        first))
(defn get-user [id]
  (-> (select users
              (where {:id id})
              (limit 1))
      first))

#_(defn add-image [userid name]
    (sql/with-db-transaction [conn db]
      (if (-> (sql/query conn ["select userid from images where userid = ? and name = ?" userid name])
              empty?)
        (sql/insert! conn :images {:userid userid :name name})
        (throw
          (Exception. "You have already uploaded an image with the same name")))))
(defn add-image [userid name]
  (transaction
    (if (empty? (select images
                        (where {:userid userid :name name})
                        (limit 1)))
      (insert images (values {:userid userid :name name}))
      (throw
        (Exception. "you have already uploaded an image with the same name")))))

#_(defn images-by-user [userid]
    (sql/query db ["select * from images where userid = ?" userid]))
(defn images-by-user [userid]
  (select images (where {:userid userid})))

#_(defn get-gallery-previews []
    (sql/query db ["SELECT * FROM images t
                    WHERE id = (SELECT id FROM images t2 WHERE t2.userid = t.userid LIMIT 1)"]))
(defn get-gallery-previews []
  (exec-raw ["SELECT * FROM images t
                    WHERE id = (SELECT id FROM images t2 WHERE t2.userid = t.userid LIMIT 1)" []]
            :results))

#_(defn delete-image [userid name]
    (sql/delete! db :images ["userid = ? and name = ?" userid name]))
(defn delete-image [userid name]
  (delete images (where {:userid userid :name name})))

#_(defn delete-user [userid]
    (sql/delete! db :users ["id = ?" userid]))
(defn delete-user [id]
  (delete users (where {:id id})))